﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using Runda.CMS.Common.Entity;

namespace Runda.CMS.Dal
{
    public class StockDal : DalBase
    {
        public DataTable QueryStockInDetail(string StockInID)
        {
            String sqlSb = "select StockInID,AccessoryID,AccessoryName,Unit,AccessoryCompanyNum,sum(quantity) as Count,StockSource,POSID from Cms_Wsm_AccessoryStock where StockInID=@StockInID group by StockInID,AccessoryID,AccessoryName,Unit,AccessoryCompanyNum,StockSource,POSID";
            
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            CurrentDatabase.AddInParameter(dbCommand, "StockInID", DbType.String, StockInID);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable QueryPODetail(string POID)
        {
            String sqlSb = "select * from Cms_Wsm_POS where POID=@POID";

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            CurrentDatabase.AddInParameter(dbCommand, "POID", DbType.String, POID);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }


        public DataTable QueryStockOutDetail(string outID)
        {
            String sqlSb = @"select c.PreBackdate,a.OutID,a.AccessoryID,b.CompanyInstrumentCode as AccessoryCompanyNum,b.AccessoryName,b.AccessoryStandard,b.Unit,a.Quantity as Count,a.Receiver,a.StockSource,u.UserName as ReceiverName,a.StockID,c.AccessoryCode from dbo.Cms_Wsm_StockInOut a
                            inner join Cms_Wsm_AccessoryStock c on a.stockID =c.stockID
                            inner join Cms_Wsm_AccessoryInfo b on a.AccessoryID = b.AccessoryID
                            inner join Cms_Sys_User u on u.UserID = a.Receiver where a.OutID = @OutID";


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            CurrentDatabase.AddInParameter(dbCommand, "OutID", DbType.String, outID);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable getStockcount(string accessoryID)
        {
            String sqlSb = "select distinct CoStockquantity as Count from vw_StockQuery where accessoryID=@AccessoryID";


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            CurrentDatabase.AddInParameter(dbCommand, "AccessoryID", DbType.String, accessoryID);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable GetStockView(string WhereClause)
        {
            string sql = "select * from vw_StockQuery where 1=1 {0}";

            sql = string.Format(sql, WhereClause);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql); 
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        //查询入库明细
        public DataTable QueryAccessoryStock(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select  
                AccessoryCompanyNum
                ,AccessoryName
                ,Unit
                ,AccessoryCode
                ,case AccessoryStatus when 0 then '在库' when 1 then '借出' when 2 then '使用中' end AccessoryStatus
                from 
                Cms_Wsm_AccessoryStock where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
                  FROM ({0}) as A
                )
                SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
                select count(*) from ({1}) as B 
                ";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //查询采购订单
        public DataTable QueryAccessoryPOMaster(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select 
                PONO
                ,PODate
                ,POFactory
                ,ApplyMan
                ,POType
                ,Checkman
                ,Checktime
                ,CheckStatus
                ,Memo
                ,POID
                ,sum(Quantity) as Quantity
                ,sum(QuantityInStock) as QuantityInStock
                ,case when (sum(QuantityInStock)=0 or sum(QuantityInStock) is null) then '未到货' when sum(QuantityInStock)>0 and sum(QuantityInStock)<sum(Quantity) then '部分到货' else '已到货' end stockStatus
                from (select 
                a.PONO
                ,a.PODate
                ,a.POFactory
                ,a.ApplyMan
                ,a.POType
                ,a.Checkman
                ,a.Checktime
                ,a.CheckStatus
                ,a.Memo
                ,b.POSID
                ,b.AccessoryCompanyNumber
                ,b.AccessoryName
                ,b.Unit
                ,b.Quantity
                ,b.IsClose
                ,Total.QuantityInStock
                ,a.POID
                ,b.AccessoryID
                ,case when (QuantityInStock=0 or QuantityInStock is null) then '未到货' when QuantityInStock>0 and QuantityInStock<Quantity then '部分到货' else '已到货' end stockStatus
                from 
                Cms_Wsm_PO a
                inner join Cms_Wsm_POS b on a.POID= b.POID
                left join 
                (
					select POSID,SUM(quantity) as QuantityInStock from Cms_Wsm_AccessoryStock where POSID is not null group by POSID
					) Total on Total.POSID = b.POSID
                ) as za  where 1=1 {0} 
                group by PONO
                ,PODate
                ,POFactory
                ,ApplyMan
                ,POType
                ,Checkman
                ,Checktime
                ,CheckStatus
                ,Memo
                ,POID");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
                  FROM ({0}) as A
                )
                SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
                select count(*) from ({1}) as B 
                ";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryAccessoryPO(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select * from (select 
                a.PONO
                ,a.PODate
                ,a.POFactory
                ,a.ApplyMan
                ,a.POType
                ,a.Checkman
                ,a.Checktime
                ,a.CheckStatus
                ,a.Memo
                ,b.POSID
                ,b.AccessoryCompanyNumber
                ,b.AccessoryName
                ,b.Unit
                ,b.Quantity
                ,b.IsClose
                ,Total.QuantityInStock
                ,a.POID
                ,b.AccessoryID
                ,case when (QuantityInStock=0 or QuantityInStock is null) then '未到货' when QuantityInStock>0 and QuantityInStock<Quantity then '部分到货' else '已到货' end stockStatus
                from 
                Cms_Wsm_PO a
                inner join Cms_Wsm_POS b on a.POID= b.POID
                left join 
                (
					select POSID,SUM(quantity) as QuantityInStock from Cms_Wsm_AccessoryStock where POSID is not null group by POSID
					) Total on Total.POSID = b.POSID
                ) as za  where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
                  FROM ({0}) as A
                )
                SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
                select count(*) from ({1}) as B 
                ";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryAccessoryPO(string whereClause)
        {
            StringBuilder sqlSb = new StringBuilder(@"select * from (select 
                a.PONO
                ,a.PODate
                ,a.POFactory
                ,a.ApplyMan
                ,a.POType
                ,a.Checkman
                ,a.Checktime
                ,a.CheckStatus
                ,a.Memo
                ,b.POSID
                ,b.AccessoryCompanyNumber
                ,b.AccessoryName
                ,b.Unit
                ,b.Quantity
                ,b.IsClose
                ,isnull(Total.QuantityInStock,0) as QuantityInStock
                ,a.POID
                ,b.AccessoryID
                from 
                Cms_Wsm_PO a
                inner join Cms_Wsm_POS b on a.POID= b.POID
                left join 
                (
					select POSID,SUM(quantity) as QuantityInStock from Cms_Wsm_AccessoryStock where POSID is not null group by POSID
					) Total on Total.POSID = b.POSID
                ) as za  where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }


        //查询公司配件、易耗件的库存
        public DataTable QueryAccessoryStockByCo(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select * from (select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,LowStockQuantity,Vendor
                        ,max(coStockquantity)+sum(isnull(case when Receiver='F6571C44-D35A-4FBA-85FD-1A96C898D215' then 0 else PersonalStockquantity end,0)) as Quantity
                        ,max(coStockquantity) as coStockquantity,sum(case when Receiver='F6571C44-D35A-4FBA-85FD-1A96C898D215' then 0 else isnull(PersonalStockquantity,0) end) as PersonalStockquantity  from vw_StockQuery
                        group by AccessoryID,CompanyInstrumentCode,AccessoryName,Unit,PresellPrice,AccessoryStandard,FactoryInstrumentCode,LowStockQuantity,Vendor
                        ) as za  where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable ExportPOList(string whereClause, string OrderBy)
        {
            StringBuilder sqlSb = new StringBuilder(@"select 
               PONO as [订购单号]
                ,PODate as [订购日期]
                ,POFactory as [订购厂家]
                ,ApplyMan as [申购人]
                ,POType as [订单性质]
                ,Memo as [备注]
                ,AccessoryCompanyNumber  as [配件号]
                ,AccessoryName  as [配件名称]
                ,Unit  as [单位]
                ,Quantity  as [数量]
                ,QuantityInStock as [到货数量]
                ,case IsClose when '1' then '已关闭' else '' end  as [状态]
                ,case CheckStatus when '0' then '未审核' else '已审核' end as [审核状态]
                ,stockStatus as [到货状态]
                ,Checkman as [审核人]
                ,Checktime as [审核时间]
                from (select 
                a.PONO 
                ,a.PODate 
                ,a.POFactory 
                ,a.ApplyMan 
                ,a.POType 
                ,a.Checkman 
                ,a.Checktime 
                ,a.CheckStatus
                ,a.Memo
                ,b.AccessoryCompanyNumber
                ,b.AccessoryName
                ,b.Unit 
                ,b.Quantity
                ,b.IsClose
                ,isnull(Total.QuantityInStock,0) as QuantityInStock
                ,case when (QuantityInStock=0 or QuantityInStock is null) then '未到货' when QuantityInStock>0 and QuantityInStock<Quantity then '部分到货' else '已到货' end stockStatus
                from 
                Cms_Wsm_PO a
                inner join Cms_Wsm_POS b on a.POID= b.POID
                left join 
                (
					select POSID,SUM(quantity) as QuantityInStock from Cms_Wsm_AccessoryStock where POSID is not null group by POSID
					) Total on Total.POSID = b.POSID
                ) as za where 1=1 {0} order by {1}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable ExportAccessoryStockList(string whereClause, string OrderBy)
        {
            StringBuilder sqlSb = new StringBuilder(@"select AccessoryCompanyNum as '配件编号'
                ,AccessoryName as '配件名称'
                ,Unit as '计量单位'
                ,AccessoryCode as '配件识别号'
                ,case AccessoryStatus when 0 then '在库' when 1 then '借出' when 2 then '使用中' end as '状态'
                from 
                Cms_Wsm_AccessoryStock where 1=1 {0} order by {1}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable ExportAccessoryStockOutList(string whereClause, string OrderBy)
        {
            StringBuilder sqlSb = new StringBuilder(@"select RequestCode as [出库单号],OutStockDate as [出库日期],memo as [备注],AccessoryCode as [配件识别号]
                ,AccessoryCompanyNum as [配件编号],AccessoryName as [配件名称],PreBackdate as [预计还入日期],UserName as [领用人] from
                (
                select a.RequestCode,a.OutID,a.OutStockDate,a.memo,c.AccessoryCode,c.AccessoryCompanyNum,c.AccessoryName,c.PreBackdate,u.UserName from Cms_Wsm_AccessoryOut a with(nolock)
                inner join Cms_Wsm_StockInOut b with(nolock) on a.OutID= b.OutID
                inner join Cms_Wsm_AccessoryStock c with(nolock) on c.StockID= b.StockID
                left join Cms_Sys_User u with(nolock) on u.UserID = b.Receiver
                ) s where 1=1 {0} order by {1}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }


        public DataTable ExportAccessoryStockByCo(string whereClause, string OrderBy)
        {
            StringBuilder sqlSb = new StringBuilder(@"select CompanyInstrumentCode as [配件编号（公司）]
,AccessoryName as [配件名称]
,FactoryInstrumentCode as [厂家编号]
,Vendor as [厂家]
,AccessoryStandard as [规格]
,Unit as [单位]
,LowStockQuantity as [最低库存]
,Quantity as [库存数量]
,coStockquantity as [仓库数量]
,PersonalStockquantity as [个人数量]
 from (select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,LowStockQuantity,Vendor
                        ,max(coStockquantity)+sum(isnull(PersonalStockquantity,0)) as Quantity
                        ,max(coStockquantity) as coStockquantity,sum(isnull(PersonalStockquantity,0)) as PersonalStockquantity  from vw_StockQuery
                        group by AccessoryID,CompanyInstrumentCode,AccessoryName,Unit,PresellPrice,AccessoryStandard,FactoryInstrumentCode,LowStockQuantity,Vendor
                        ) as za  where 1=1 {0} order by {1}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause,OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        //查询个人库存
        public DataTable QueryAccessoryStockByPersonal(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,LowStockQuantity,v.coStockquantity,v.Vendor,v.AccessoryType
                        ,isnull(PersonalStockquantity,0) as Quantity,u.UserName from vw_StockQuery v
                        inner join Cms_Sys_User u with(nolock) on u.UserID = v.Receiver
                        where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //查询配件、易耗件的库存明细
        public DataTable QueryAccessoryStockDetail(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select distinct * from (select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,AccessoryType,AccessoryTypeName
                        ,AccessoryStatus,AccessoryStatusName,AccessoryCode,Receiver,ReceiverName,OutStockDate,AccessoryUsedDate,InStockdate,vendor,PreBackdate
		                ,backdate,StockID,RegisterID,OrderCheckStatus
                        from vw_StockDetail
                        ) as za  where AccessoryCode is not null {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable CalcAccessoryStockByCo(string WhereClause)
        {
            string sqlSb = @"
                    select count(*) as countSum,sum(Quantity) as quantitySum ,sum(coStockquantity) as coStockquantity,sum(PersonalStockquantity) as PersonalStockquantity from (select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,LowStockQuantity,vendor
                        ,max(coStockquantity)+sum(isnull(PersonalStockquantity,0)) as Quantity
                        ,max(coStockquantity) as coStockquantity
                        ,sum(isnull(PersonalStockquantity,0)) as PersonalStockquantity from vw_StockQuery 
                        group by AccessoryID,CompanyInstrumentCode,AccessoryName,Unit,PresellPrice,AccessoryStandard,FactoryInstrumentCode,LowStockQuantity,vendor
                        ) as za  where 1=1 {0}";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable CalcAccessoryStockByPersonal(string WhereClause)
        {
            string sqlSb = @"
                    select count(*) as countSum,sum(isnull(PersonalStockquantity,0)) as quantitySum from vw_StockQuery v
                        inner join Cms_Sys_User u with(nolock) on u.UserID = v.Receiver where 1=1 {0}";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }


        public DataTable CalcAccessoryStock(string WhereClause)
        {
            string sqlSb = @"select count(distinct stockid) as countSum,count(distinct accessoryID) as countaccessory  from (select 
                        AccessoryID,CompanyInstrumentCode
                        ,AccessoryName,Unit,PresellPrice,AccessoryStandard
                        ,FactoryInstrumentCode,AccessoryType,AccessoryTypeName
                        ,AccessoryStatus,AccessoryStatusName,AccessoryCode,Receiver,ReceiverName,OutStockDate,AccessoryUsedDate,InStockdate,vendor,stockid,OrderCheckStatus
                        from vw_StockDetail
                        ) as za  where AccessoryCode is not null {0}";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }
        //删除服务单时，相应的配件的状态还原（个人库存-1；公司库存-0)
        public void cancelAccessoryUsage(string maintainID)
        {
            string sqlSb = @"update Cms_Wsm_AccessoryStock
                        set AccessoryStatus = case AccessoryStatus when 3 then 0 when 2 then 1 end
                        where StockID in
                        (
                        select StockID from Cms_Wsm_AccessoryReplacement where MaintainID = '{0}'
                        )";
            string sqlStr = string.Format(sqlSb.ToString(), maintainID);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
        }
        //关闭采购订单明细
        public void ClosePOS(string POSID)
        {
            string sql = @"update Cms_Wsm_POS set 
                IsClose='1' WHERE POSID=@POSID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@POSID", DbType.String, POSID);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        //审核采购订单
        public void UpdatePOcheckStatus(Guid POID, string checkStatus, string checkMan, DateTime Checktime)
        {
            string sql = @"update Cms_Wsm_PO set 
                CheckStatus='{0}',Checkman='{1}',Checktime='{2}' WHERE POID=@POID";
            sql = string.Format(sql, checkStatus, checkMan, Checktime);

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@POID", DbType.Guid, POID);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        //还入配件
        public void BackAccessory(string stockID, string BackMan, DateTime Backdate)
        {
            string sql = @"update Cms_Wsm_StockInOut set outquantity= -1 where StockID='{0}'; update Cms_Wsm_AccessoryStock set 
                AccessoryStatus=0 WHERE StockID='{0}';
                insert into Cms_Wsm_AccessoryBack(StockID,BackMan,Backdate) values('{0}','{1}','{2}')";
            sql = string.Format(sql, stockID, BackMan, Backdate);

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            //CurrentDatabase.AddInParameter(dbcommand, "@POID", DbType.Guid, POID);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }
    }
}
